
use BankTest
go

--1. 定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额
select * from BankCard 
go
-- drop proc proc_getMinMoney
create proc proc_getMinMoney
@minMoney money output
as
begin
	select @minMoney = MIN(CardMoney) from BankCard
	select CardNo,RealName,CardMoney from BankCard b
	join AccountInfo a on a.AccountId = b.AccountId
	where CardMoney = @minMoney
end
go

declare @minMoney money 
exec proc_getMinMoney @minMoney output



--2. 模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作
select * from CardExchange
go
create proc proc_getInMoney
@cardNo varchar(50),
@inMoney money
as
begin
	insert into CardExchange values(@cardNo,@inMoney,0,GETDATE())

end
go
--
declare @cardNo varchar(50) = '6225125478544587',
		@inMoney money = 8848
exec proc_getInMoney @cardNo,@inMoney
select * from CardExchange



--3. 模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1
select * from BankCard
go
-- drop proc proc_outMoney

create proc proc_outMoney
@cardNum varchar(50),
@outMoney money,
@backNum int output
as
begin
	select @backNum = (case when CardMoney >= @outMoney then 1 else -1 end) from BankCard 
	where CardNo = @cardNum
	update BankCard set CardMoney -= @outMoney where CardNo = @cardNum
end
go
--执行
declare @cardNum varchar(50) = '6225547854125656',
@outMoney money = 1000,
@backNum int 
exec proc_outMoney @cardNum,@outMoney,@backNum output
select @backNum
select * from BankCard


--4. 查询出某时间段的银行存取款信息以及存款总金额**，取款总金额，
--传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。
select * from CardExchange  --交易记录

--	drop proc proc_getBankMessage
go
create proc proc_getBankMessage
@openTime smalldatetime,
@overTime smalldatetime,
@InSum money output,
@OutSum money output
as
begin
	select @InSum = SUM(MoneyInBank),@OutSum = SUM(MoneyOutBank) from CardExchange 
	where ExchangeTime between @openTime and @overTime
end
go

declare @openTime smalldatetime = '2022-09-07 14:30:00',
@overTime smalldatetime = '2022-09-27 08:19:00',
@InSum money,@OutSum money

exec proc_getBankMessage @openTime,@overTime,@InSum output,@OutSum output
select @InSum 存款总额,@OutSum 取款总额


--5. **密码升级**，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码
--(提示：随机生成 0-9 的整数： float(rand()*10))  rand():随机生成0.0-1.0的小数 float:向下取整）

-- drop proc proc_pwd
-- update BankCard set CardPwd = '123456' where AccountId = 1
go
create proc proc_pwd
@cardNo varchar(50),
@pwd varchar(50)
as
begin
	if((select CardNo from BankCard where CardNo = @cardNo) != ' ' 
		and @pwd = (select CardPwd from BankCard where CardNo = @cardNo and @pwd = CardPwd))
		begin
			if(len(@pwd) < 8)
			begin
				update BankCard set CardPwd = CONCAT(ROUND(99999+RAND()*99999,0),ROUND(RAND()*9,0),ROUND(RAND()*9,0)) where CardNo = @cardNo
			end
		end
	else
		begin
			print '错误了X_X'
		end
end
go
-- 执行
declare @cardNo varchar(50) = '6225125478544587',@pwd varchar(50) = '123456'
exec proc_pwd @cardNo,@pwd
select * from BankCard




